import json

from django.db import connection
from django.http import JsonResponse
from django.shortcuts import render
from rest_framework.decorators import api_view, permission_classes
from rest_framework.permissions import IsAuthenticated

from apps.utils.mysql_utils import DBUtil
from apps.utils.response import response_result


@api_view(('POST',))
@permission_classes((IsAuthenticated,))
def basic_info(request):
    if request.method == 'POST':
        request.params = json.loads(request.body)
        action = request.params['action']
        if action == 'basic_info_season':
            return basic_info_season(request)
        if action == 'basic_info_year':
            return basic_info_year(request)
        if action == 'basic_info_total':
            return basic_info_total()


@api_view(('POST',))
@permission_classes((IsAuthenticated,))
def dept_refund(request):
    if request.method == 'POST':
        request.params = json.loads(request.body)
        action = request.params['action']
        if action == 'dept_year_amount':
            return dept_year_amount(request)
        if action == 'dept_season_amount':
            return dept_season_amount(request)


@api_view(('POST',))
@permission_classes((IsAuthenticated,))
def dealer_refund_top(request):
    if request.method == 'POST':
        request.params = json.loads(request.body)
        action = request.params['action']
        if action == 'dealer_refund_year_top':
            return dealer_refund_year_top(request)
        if action == 'dealer_refund_season_top':
            return dealer_refund_season_top(request)
        if action == 'dealer_refund_total_top':
            return dealer_refund_total_top()


@api_view(('POST',))
@permission_classes((IsAuthenticated,))
def dealer_refund_wrong_type(request):
    # auth_log().log_check(request)
    if request.method == 'POST':
        request.params = json.loads(request.body)
        action = request.params['action']
        if action == 'dealer_wrong_refund_type':
            return dealer_wrong_refund_type(request)

@api_view(('POST',))
@permission_classes((IsAuthenticated,))
def scheme_amount_top(request):
    # auth_log().log_check(request)
    # if log_check(request) is False:
    #     return JsonResponse({
    #         'ret': 302,
    #         'msg': '未登录'},
    #         status=302)
    if request.method == 'POST':
        request.params = json.loads(request.body)
        action = request.params['action']
        if action == 2:
            return scheme_amount_season_top(request)
        if action == 1:
            return scheme_amount_year_top(request)
        if action == 0:
            return scheme_amount_total_top()


def basic_info_season(request):
    """
    季度基本信息接口
    :return:所有季度的信息
    """
    seasoncode = request.params['seasoncode']
    dbs = DBUtil()

    total_amount_sql = "select if(total_amount is null ,0,total_amount) from (select sum(realrefund) as total_amount " \
                       "from `RB_Settle_Check` where getseason(submitdate)=%s union all select sum(订单产品总价) as  total_amount from " \
                       "datacheat_1 where 存疑类型 != '' and getseason(策划案提交时间) = %s ) a" % (seasoncode, seasoncode)

    total_amount_sql_last_season = "select if(total_amount is null ,0,total_amount) from (select sum(realrefund) as total_amount " \
                                   "from `RB_Settle_Check` where getseason(submitdate)=getlastseasonsecond(%s) union all select sum(订单产品总价) as  total_amount from " \
                                   "datacheat_1 where 存疑类型 != '' and getseason(策划案提交时间) = getlastseasonsecond(%s) ) a" % (
                                       seasoncode, seasoncode)
    print(total_amount_sql)
    print(total_amount_sql_last_season)
    t_res = dbs.get_all(total_amount_sql)
    res_0 = (t_res[0][0] if t_res[0][0] is not None else 0) + (t_res[1][0] if t_res[1][0] is not None else 0)
    res_1 = t_res[0][0] if t_res[0][0] is not None else 0
    res_2 = t_res[1][0] if t_res[1][0] is not None else 0
    o_res = dbs.get_all(total_amount_sql_last_season)
    res_4 = (o_res[0][0] if o_res[0][0] is not None else 0) + (o_res[1][0] if o_res[1][0] is not None else 0)
    res_5 = o_res[0][0] if o_res[0][0] is not None else 0
    res_6 = o_res[1][0] if o_res[1][0] is not None else 0
    ratio_0 = (res_0 - res_4) / (res_4 if res_4 != 0 else 1)
    ratio_1 = (res_1 - res_5) / (res_5 if res_5 != 0 else 1)
    ratio_2 = (res_2 - res_6) / (res_6 if res_6 != 0 else 1)

    # 0为异常返款金额
    res_dict_0 = {"title": "0", "value": res_0, "ratio": ratio_0}
    # 1为订单重复金额
    res_dict_1 = {"title": "1", "value": res_1, "ratio": ratio_1}
    # 2为数据篡改金额
    res_dict_2 = {"title": "2", "value": res_2, "ratio": ratio_2}
    res_list = [res_dict_0, res_dict_1, res_dict_2]
    return JsonResponse(res_list, safe=False)


def basic_info_year(request):
    """
    年度基本信息接口
    :return:所有年度的信息
    """
    year = request.params['year']

    dbs = DBUtil()

    total_amount_sql = "select * from (" \
                       "select sum(realrefund) as total_amount from `RB_Settle_Check` where year (submitdate)=%s " \
                       "union all select sum(订单产品总价) as  total_amount from datacheat_1 " \
                       "where 存疑类型 != \'\' and year (策划案提交时间) = %s ) a" % (year, year)
    total_amount_sql_last_year = "select * from (" \
                                 "select sum(realrefund) as total_amount from `RB_Settle_Check` where year (submitdate)=%s " \
                                 "union all select sum(订单产品总价) as  total_amount from datacheat_1 " \
                                 "where 存疑类型 != \'\' and year (策划案提交时间) = %s ) a" % (year - 1, year - 1)
    print(total_amount_sql)
    t_res = dbs.get_all(total_amount_sql)
    res_0 = (t_res[0][0] if t_res[0][0] is not None else 0) + (t_res[1][0] if t_res[1][0] is not None else 0)
    res_1 = t_res[0][0] if t_res[0][0] is not None else 0
    res_2 = t_res[1][0] if t_res[1][0] is not None else 0
    o_res = dbs.get_all(total_amount_sql_last_year)
    res_4 = (o_res[0][0] if o_res[0][0] is not None else 0) + (o_res[1][0] if o_res[1][0] is not None else 0)
    res_5 = o_res[0][0] if o_res[0][0] is not None else 0
    res_6 = o_res[1][0] if o_res[1][0] is not None else 0
    ratio_0 = (res_0 - res_4) / (res_4 if res_4 != 0 else 1)
    ratio_1 = (res_1 - res_5) / (res_5 if res_5 != 0 else 1)
    ratio_2 = (res_2 - res_6) / (res_6 if res_6 != 0 else 1)

    # 0为异常返款金额
    res_dict_0 = {"title": "0", "value": res_0, "ratio": ratio_0}
    # 1为订单重复金额
    res_dict_1 = {"title": "1", "value": res_1, "ratio": ratio_1}
    # 2为数据篡改金额
    res_dict_2 = {"title": "2", "value": res_2, "ratio": ratio_2}

    res_list = [res_dict_0, res_dict_1, res_dict_2]
    return JsonResponse(res_list, safe=False)


def basic_info_total():
    """
    累计基本信息接口
    :return:所有年度的信息
    """
    dbs = DBUtil()
    total_amount_sql = "select sum(total_amount) " \
                       "from(select sum(realrefund) as total_amount from RB_Settle_Check " \
                       "UNION all " \
                       "select sum(订单产品总价) as  total_amount from datacheat_1 ) temp"
                       # "select sum(订单产品总价) as  total_amount from datacheat_1 where 存疑类型 != \'\') temp"
    order_repeat_sql = "select sum(realrefund) from RB_Settle_Check"
    # data_cheat_sql = "select sum(订单产品总价) from datacheat_1 where 存疑类型!=\'\'"
    data_cheat_sql = "select sum(订单产品总价) from datacheat_1 "

    t_res = dbs.get_all(total_amount_sql)

    o_res = dbs.get_all(order_repeat_sql)

    d_res = dbs.get_all(data_cheat_sql)
    # 0为异常返款金额
    res_dict_0 = {"title": "0", "value": round(t_res[0][0]/10000, 2), "ratio": None}
    # 1为订单重复金额
    res_dict_1 = {"title": "1", "value": round(o_res[0][0]/10000, 2), "ratio": None}
    # 2为数据篡改金额
    res_dict_2 = {"title": "2", "value": round(d_res[0][0]/10000, 2), "ratio": None}

    res_list = [res_dict_0, res_dict_1, res_dict_2]
    return JsonResponse(res_list, safe=False)


def dept_year_amount(request):
    """
    各部门返款情况分析
    :return: 所有年度每个部门返款情况
    """

    try:
        year = request.params['year']
        with connection.cursor() as cursor:
            sql = r"SELECT left(getseason(submitdate),4) as sub_year,settlement_dept,sum(realrefund) as total_refund " \
                  r"FROM `RB_Settle_Check` where left(getseason(submitdate),4)=%s group by " \
                  r"left(getseason(submitdate),4),settlement_dept" \
                  r" order by total_refund desc" % year
            print(sql)
            cursor.execute(sql)
            rawData = cursor.fetchall()
            col_names = [desc[0] for desc in cursor.description]
            result = []
            for row in rawData:
                objDict = {}
                for index, value in enumerate(row):
                    objDict[col_names[index]] = value
                result.append(objDict)
        return response_result.res_success(result)
    except ValueError as err:
        return response_result.res_faile(err)


def dept_season_amount(request):
    """
    各部门返款情况分析
    :return: 所有年度每个部门返款情况
    """
    try:
        season_code = request.params['season_code']
        dept = request.params['dept']
        with connection.cursor() as cursor:
            sql = "select right(season_code,1) season_code,ROUND(sum(realrefund),2) as season_refund from ( select getseason(submitdate) season_code,realrefund from " \
                  "RB_Settle_Check where settlement_dept = '%s' ) as tmp where left(season_code,4)= '%s' group by season_code order by season_refund desc" % (
                      dept, season_code)

            print(sql)
            cursor.execute(sql)
            rawData = cursor.fetchall()
            col_names = [desc[0] for desc in cursor.description]
            result = []
            for row in rawData:
                objDict = {}
                for index, value in enumerate(row):
                    objDict[col_names[index]] = value
                result.append(objDict)
        return response_result.res_success(result)
    except ValueError as err:
        return response_result.res_faile(err)


def dealer_refund_year_top(request):
    """
    经销商异常返款年度排名TOP10
    :return: 返回累计排名前十的经销商
    """
    year = request.params['year']
    with connection.cursor() as cursor:
        sql = "SELECT year (submitdate) as sub_year,agent,sum(realrefund) as total_refund " \
              "FROM `RB_Settle_Check` where year (submitdate)=%s group by " \
              "year (submitdate),agent" \
              " order by total_refund desc" % year
        print(sql)
        cursor.execute(sql)
        rawData = cursor.fetchall()
        col_names = [desc[0] for desc in cursor.description]
        result = []
        for row in rawData:
            objDict = {}
            for index, value in enumerate(row):
                objDict[col_names[index]] = value
            result.append(objDict)
    return JsonResponse(result, safe=False)


def dealer_refund_season_top(request):
    """
    经销商异常返款年度排名TOP10
    :return: 返回累计排名前十的经销商
    """
    season_code = request.params['season_code']
    with connection.cursor() as cursor:
        sql = "select agent,season_code,sum(realrefund) as season_refund\
                from(\
                select agent,case (DATE_FORMAT(submitdate,'%%m')-1) div 3 \
                    when 0 then CONCAT(DATE_FORMAT(submitdate,'%%Y'),'4')\
                    when 1 then CONCAT(DATE_FORMAT(submitdate,'%%Y'),'1')\
                    when 2 then CONCAT(DATE_FORMAT(submitdate,'%%Y'),'2')\
                    when 3 then CONCAT(DATE_FORMAT(submitdate,'%%Y'),'3')\
                    END as season_code,realrefund\
                    from RB_Settle_Check) as tmp\
                where season_code = %s\
                group by agent,season_code\
                order by season_refund desc" % season_code
        cursor.execute(sql)
        rawData = cursor.fetchall()
        col_names = [desc[0] for desc in cursor.description]
        result = []
        for row in rawData:
            objDict = {}
            for index, value in enumerate(row):
                objDict[col_names[index]] = value
            result.append(objDict)
    return JsonResponse(result, safe=False)


def dealer_refund_total_top():
    """
     经销商异常返款累计排名TOP10
     :return: 返回累计排名前十的经销商
     """
    with connection.cursor() as cursor:
        sql = "SELECT agent,sum(realrefund) as total_refund " \
              "FROM `RB_Settle_Check` group by agent order by total_refund desc"
        cursor.execute(sql)
        rawData = cursor.fetchall()
        col_names = [desc[0] for desc in cursor.description]
        result = []
        for row in rawData:
            objDict = {}
            for index, value in enumerate(row):
                objDict[col_names[index]] = value
            result.append(objDict)
    return JsonResponse(result, safe=False)


# 经销商返款异常类型
def dealer_wrong_refund_type(request):
    """
    经销商异常返款类型
    :return: 经销商每年异常返款数量
    """
    year = request.params['year']
    with connection.cursor() as cursor:
        sql_str = 'select * from (select right(season_code,1) season_code,\'0\' as wrong_type,count(1) as type_num from' \
                  ' ( select getseason(submitdate) season_code from RB_Settle_Check) as tmp where left(season_code,4) = %s group by season_code  ' \
                  'union all ' \
                  'select right(season_code,1) season_code,\'1\' as wrong_type,count(1) as type_num from ( select getseason(`策划案提交时间`) season_code from' \
                  ' datacheat_1) as tmp where left(season_code,4) = %s group by season_code ) aa' % (year, year)
        print(sql_str)
        cursor.execute(sql_str)
        rawData = cursor.fetchall()
        col_names = [desc[0] for desc in cursor.description]
        result = []
        for row in rawData:
            objDict = {}
            for index, value in enumerate(row):
                objDict[col_names[index]] = value
            result.append(objDict)
    return JsonResponse(result, safe=False)


def scheme_amount_season_top(request):
    """
    策划案金额TOP10
    :return: 返回累计排名前十的策划案
    """
    season_code = request.params['season_code']
    with connection.cursor() as cursor:
        sql = "select settlement_name,settleowner,sum(realrefund) as season_refund,if(sum(is_case)!=0,0,1) is_case from" \
              " (select settlement_name,settleowner,realrefund,if(cr.markkey = '' or cr.markkey is null,1,0) as is_case from " \
              "RB_Settle_Check rbsc left join case_relationship cr on rbsc.ordercode = cr.markkey where getseason(submitdate) = %s )" \
              "aa group by settlement_name,settleowner order by season_refund desc" % season_code
        print(sql)
        cursor.execute(sql)
        rawData = cursor.fetchall()
        col_names = [desc[0] for desc in cursor.description]
        result = []
        for row in rawData:
            objDict = {}
            for index, value in enumerate(row):
                objDict[col_names[index]] = value
            result.append(objDict)
    return JsonResponse(result, safe=False)


def scheme_amount_year_top(request):
    """
    经销商异常返款年度排名TOP10
    :return: 返回累计排名前十的经销商
    """
    year = request.params['year']
    with connection.cursor() as cursor:
        sql = "select settlement_name,settleowner,sum(realrefund) as total_refund,if(sum(is_case)!=0,0,1) is_case" \
              " from (SELECT settlement_name,settleowner,realrefund,if(cr.markkey = '' or cr.markkey is null,1,0) as is_case " \
              "FROM `RB_Settle_Check` rbsc left join case_relationship cr on rbsc.ordercode = cr.markkey where year (submitdate)=%s ) " \
              "aa group by settlement_name,settleowner order by total_refund desc" % year
        print(sql)
        cursor.execute(sql)
        rawData = cursor.fetchall()
        col_names = [desc[0] for desc in cursor.description]
        result = []
        for row in rawData:
            objDict = {}
            for index, value in enumerate(row):
                objDict[col_names[index]] = value
            result.append(objDict)
    return JsonResponse(result, safe=False)


def scheme_amount_total_top():
    """
    策划案异常返款累计排名TOP10
    :return: 返回累计排名前十的经销商
    """
    with connection.cursor() as cursor:
        sql = "select settlement_name,settleowner,sum(realrefund) as total_refund,if(sum(is_case)!=0,0,1) is_case from " \
              "(SELECT settlement_name,settleowner,realrefund,if(cr.markkey = '' or cr.markkey is null,1,0) as is_case FROM " \
              "`RB_Settle_Check` rbsc left join case_relationship cr on rbsc.ordercode = cr.markkey ) aa group by settlement_name,settleowner " \
              "order by total_refund desc"
        cursor.execute(sql)
        rawData = cursor.fetchall()
        col_names = [desc[0] for desc in cursor.description]
        result = []
        for row in rawData:
            objDict = {}
            for index, value in enumerate(row):
                objDict[col_names[index]] = value
            result.append(objDict)
    return JsonResponse(result, safe=False)
